package com.zhang.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.*;

public class ExcelToCreateTableWithComments {
    public static void main(String[] args) {
        String excelFilePath = "F:\\2025\\250320\\导出数据_20250320185152.xls";
        String outputFilePath = "F:\\2025\\250320\\create_table_statements.sql";
        try {
            generateCreateTableStatements(excelFilePath, outputFilePath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void generateCreateTableStatements(String excelFilePath, String outputFilePath) throws IOException {
        Map<String, List<String>> tableColumns = new HashMap<>();
        Map<String, String> tableComments = new HashMap<>();
        Map<String, Map<String, String>> columnComments = new HashMap<>();

        try (FileInputStream fis = new FileInputStream(new File(excelFilePath));
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            boolean isHeader = true;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (isHeader) {
                    isHeader = false;
                    continue;
                }
                String tableName = getCellValueAsString(row.getCell(2));
                String columnName = getCellValueAsString(row.getCell(3));
                String columnType = getCellValueAsString(row.getCell(15));
                String isNullable = getCellValueAsString(row.getCell(6));
                String columnDefault = getCellValueAsString(row.getCell(5));
                String columnKey = getCellValueAsString(row.getCell(16));
                String extra = getCellValueAsString(row.getCell(17));
                String tableComment = getCellValueAsString(row.getCell(20));
                String columnComment = getCellValueAsString(row.getCell(19));

                StringBuilder columnDefinition = new StringBuilder();
                columnDefinition.append(columnName).append(" ").append(columnType);

                if ("NO".equals(isNullable)) {
                    columnDefinition.append(" NOT NULL");
                }

                if (!"".equals(columnDefault)) {
                    columnDefinition.append(" DEFAULT ").append(columnDefault);
                }

                if ("PRI".equals(columnKey)) {
                    columnDefinition.append(" PRIMARY KEY");
                }

                if (!"".equals(extra)) {
                    columnDefinition.append(" ").append(extra);
                }

                if (!"".equals(columnComment)) {
                    columnDefinition.append(" COMMENT '").append(columnComment).append("'");
                }

                tableColumns.computeIfAbsent(tableName, k -> new ArrayList<>()).add(columnDefinition.toString());
                tableComments.put(tableName, tableComment);
                columnComments.computeIfAbsent(tableName, k -> new HashMap<>()).put(columnName, columnComment);
            }
        }

        try (FileWriter writer = new FileWriter(outputFilePath)) {
            for (Map.Entry<String, List<String>> entry : tableColumns.entrySet()) {
                String tableName = entry.getKey();
                List<String> columns = entry.getValue();
                String tableComment = tableComments.get(tableName);
                String createTableStatement = generateCreateTableStatement(tableName, columns, tableComment);
                writer.write(createTableStatement);
                writer.write("\n\n");
            }
            System.out.println("建表语句已成功输出到文件: " + outputFilePath);
        }
    }

    public static String generateCreateTableStatement(String tableName, List<String> columns, String tableComment) {
        StringBuilder createTable = new StringBuilder();
        createTable.append("CREATE TABLE ").append(tableName).append(" (\n");

        for (int i = 0; i < columns.size(); i++) {
            createTable.append("    ").append(columns.get(i));
            if (i < columns.size() - 1) {
                createTable.append(",\n");
            }
        }

        if (!"".equals(tableComment)) {
            createTable.append("\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '").append(tableComment).append("';");
        } else {
            createTable.append("\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        }
        return createTable.toString();
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}    